import 'package:cashbook/custom/DatumLegendWithMeasures.dart';
import 'package:cashbook/custom/SimpleBarChart.dart';
import 'package:cashbook/utils/db_util/dbmanager.dart';
import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart';

class Accounts {
  int autoId;
  String uUUID;
  String title;
  String type;
  int createTime;
  String createYearAndMouth;
  String money;
  String remark;

  static final String SR = 'SR';
  static final String ZC = 'ZC';

  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      columnUUUID: uUUID,
      columnTitle: title,
      columnType: type,
      columnCreateTime: createTime,
      columnCreateYearAndMouth: createYearAndMouth,
      columnMoney: money,
      columnRemark: remark,
    };
    if (autoId != null) {
      map[columnAutoId] = autoId;
    }
    return map;
  }

  Accounts();

  Accounts.fromMap(Map<String, dynamic> map) {
    autoId = map[columnAutoId];
    uUUID = map[columnUUUID];
    title = map[columnTitle];
    type = map[columnType];
    createTime = map[columnCreateTime];
    createYearAndMouth = map[columnCreateYearAndMouth];
    money = map[columnMoney];
    remark = map[columnRemark];
  }

  @override
  String toString() {
    return 'Accounts{autoId: $autoId, uUUID: $uUUID, title: $title, type: $type, createTime: $createTime, createYearAndMouth: $createYearAndMouth, money: $money, remark: $remark}';
  }
}

final String tableUser = 'accounts';
final String columnAutoId = 'autoId';
final String columnUUUID = 'uUUID';
final String columnTitle = 'title';
final String columnType = 'type';
final String columnCreateTime = 'createTime';
final String columnCreateYearAndMouth = 'createYearAndMouth';
final String columnMoney = 'money';
final String columnRemark = 'remark';

class AccountsProvider {
  Database db;

  String _createTableSql = '''
  create table ${tableUser} (
    ${columnAutoId} integer primary key autoincrement,
    ${columnUUUID} text not null,
    ${columnTitle} text not null,
    ${columnType} text not null,
    ${columnCreateTime} integer not null,
    ${columnCreateYearAndMouth} text not null,
    ${columnMoney} text not null, 
    ${columnRemark} text 
     )
  ''';

  Future createTable() async {
    DBManager.createTable(tableUser, _createTableSql);
  }

  insert(Accounts accounts) async {
    int autoId = -1;
    autoId = await DBManager.getDb().insert(tableUser, accounts.toMap());
    return autoId;
  }

  findAllAccounts() async {
    List<Map> maps = await DBManager.getDb()
        .rawQuery('SELECT * FROM  ${tableUser} ORDER BY createTime DESC');
    List<Accounts> list = List();
    for (int i = 0; i < maps.length; i++) {
      Accounts accounts = Accounts.fromMap(maps[i]);
      list.add(accounts);
    }
    return list;
  }

  findAllAccountsByYearAndMouth(int year, int mouth) async {
    List<Map> maps = await DBManager.getDb().rawQuery(
        "SELECT * FROM   ${tableUser} WHERE createYearAndMouth ='${year}/${mouth}' ORDER BY createTime DESC ");
    List<Accounts> list = List();
    for (int i = 0; i < maps.length; i++) {
      Accounts accounts = Accounts.fromMap(maps[i]);
      list.add(accounts);
    }
    return list;
  }

  //根据年 月 分组查询数据
  findByYearAndMonth(int year, int month, {String type = 'ZC'}) async {
    var sql = '''SELECT
                    sum(accounts.money) AS sales,
                    strftime('%m',(SELECT date(accounts.createTime/1000,'unixepoch','localtime'))) AS year
                  FROM
                    accounts
                  WHERE
                    accounts.createYearAndMouth LIKE '${year}%'  AND accounts.type = '${type}'
                  GROUP BY
                    accounts.createYearAndMouth''';
    print('======> 执行sql = > ${sql}');
    List<Map> maps = await DBManager.getDb().rawQuery(sql);
    List<OrdinalSales> list = [];
    for (int i = 0; i < maps.length; i++) {
      OrdinalSales ordinalSales = OrdinalSales.fromMap(maps[i]);
      list.add(ordinalSales);
    }
    return list;
  }

  findBySZ(int year) async{
    var sql = '''SELECT
                    sum(accounts.money) AS sales,
                    strftime('%m',(SELECT date(accounts.createTime/1000,'unixepoch','localtime'))) AS year,
                    type
                  FROM
                    accounts
                  WHERE
                    accounts.createYearAndMouth LIKE '${year}%'
                  GROUP BY
                    accounts.type''';
    print('======> 执行sql = > ${sql}');
    List<Map> maps = await DBManager.getDb().rawQuery(sql);
    List<LinearSales> list = [];
    for (int i = 0; i < maps.length; i++) {
      LinearSales linearSales = LinearSales.fromMap(maps[i]);
      list.add(linearSales);
    }
    return list;
  }
}
//List<Map> maps = await DBManager.getDb().query(tableUser);
//
//List<User> userList = List();
//for (int i = 0; i < maps.length; i++) {
//User u = User.fromMap(maps[i]);
//if (u != null) userList.add(u);
//print(u.toString());
//}
//return userList;
